package com.pms.web;

import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.mapper.Wrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.pms.entity.*;
import com.pms.exception.R;
import com.pms.exception.RRException;
import com.pms.service.*;
import com.pms.util.DateUtil;
import com.pms.util.ExcelReadUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import com.pms.controller.BaseController;
import org.springframework.web.bind.annotation.RequestMapping;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.*;

/**
 * 1）只支持 excel表格添加
 * 2）卡号添加后 无法修改，无法删除
 */
@RestController
@RequestMapping("estateUnitCardBase")
@Api(value = "卡片导入", description = "卡片导入管理界面接口")
public class EstateUnitCardController extends BaseController {
    Logger logger = LoggerFactory.getLogger(getClass());
    @Autowired
    IEstateUnitCardService estateUnitCardService;
    @Autowired
    IEstateDoorCardService estateDoorCardService;
    @Autowired
    private IEstateAgencyService agencyService;
    @Autowired
    private IUnitBuildingService unitBuildingService;
    @Autowired
    private IEstateEqFactoryService estateEqFactoryService;
    @Value("${excel.read.templatepath}")
    private String excelTemplatePath;
    @Value("${excel.write.errDaHaoCardPath}")
    private String excelErrDaHaoCardPath;

    @ApiOperation(value = "分页查询卡片导入信息")
    @RequestMapping(value = "/readPage", method = RequestMethod.POST)
    @ApiImplicitParams({
            @ApiImplicitParam(name = "pageSize", value = "请求条数", required = false, dataType = "int", paramType = "form"),
            @ApiImplicitParam(name = "pageNum", value = "请求页数", required = false, dataType = "int", paramType = "form"),
            @ApiImplicitParam(name = "communityId", value = "社区id", required = true, dataType = "long", paramType = "form"),
            @ApiImplicitParam(name = "unitId", value = "单元id", required = false, dataType = "long", paramType = "form"),
            @ApiImplicitParam(name = "cardNum", value = "卡号-模糊查询", required = false, dataType = "string", paramType = "form"),
            @ApiImplicitParam(name = "cardState", value = "卡号状态(1-已录入(默认),2-待发卡,3-已发卡)", required = false, dataType = "int", paramType = "form"),
            @ApiImplicitParam(name = "doorId", value = "门id", required = false, dataType = "long", paramType = "form"),
    })
    public R readPage(Integer pageSize, Integer pageNum, Long communityId, Long unitId, String cardNum, Integer cardState, Long doorId) {
        parameterIsNull(communityId, "社区id不能为空");
        if (null == pageNum || pageNum < 1) {
            pageNum = 1;
        }
        if (null == pageSize || pageSize < 1) {
            pageSize = 10;
        }
        if (cardState == null || cardState < 2 || cardState > 3) {
            cardState = 1;
        }
        if (cardState == 1) {//查询已录入
            Page<EstateUnitCard> page = new Page<EstateUnitCard>(pageNum, pageSize);
            Wrapper<EstateUnitCard> wp = new EntityWrapper<EstateUnitCard>();
            wp.eq("community_id", communityId);
            if (unitId != null) {
                wp.eq("unit_id", unitId);
            }
            if (StringUtils.isNotBlank(cardNum)) {
                wp.like("card_num_ten", cardNum);
            }
            wp.orderBy("create_time",false);//根据录入时间降序查询
            Page<EstateUnitCard> returnPage = estateUnitCardService.selectPage(page, wp);
            return R.ok().put("data", returnPage).putMpPageDescription().putDescription(EstateUnitCard.class);
        }
        if (cardState == 2) {//查询待发卡
            String leftJoinWhere = "";
            Page<EstateUnitCard> page = new Page<EstateUnitCard>(pageNum, pageSize);
            Wrapper<EstateUnitCard> wp = new EntityWrapper<EstateUnitCard>();
            wp.eq("euc.community_id", communityId);
            if (unitId != null) {
                wp.eq("euc.unit_id", unitId);
            }
            if (null != doorId) {
                leftJoinWhere = " and edc.door_id = "+doorId;
            }
            if (StringUtils.isNotBlank(cardNum)) {
                wp.like("euc.cardNumTen", cardNum);
            }
            wp.and("edc.door_card_id is null");
            wp.orderBy("euc.create_time",true);//根据录入时间升序查询
            Page<EstateUnitCard> returnPage = estateDoorCardService.selectWillUseCardPage(leftJoinWhere,page, wp);
            return R.ok().put("data", returnPage).putMpPageDescription().putDescription(EstateUnitCard.class);
        }
        if (cardState == 3) {//查询已发卡
            Page<EstateDoorCard> page = new Page<EstateDoorCard>(pageNum, pageSize);
            Wrapper<EstateDoorCard> wp = new EntityWrapper<EstateDoorCard>();
            if (null != communityId) {
                wp.eq("community_id", communityId);
            }
            if (null != unitId) {
                wp.eq("unit_id", unitId);
            }
            if (null != doorId) {
                wp.eq("door_id", doorId);
            }
            if (StringUtils.isNotBlank(cardNum)) {
                wp.like("cardNumTen", cardNum);
            }
            wp.orderBy("create_time",false);//根据发卡时间降序查询
            Page<EstateDoorCard> returnPage = estateDoorCardService.selectPage(page, wp);
            return R.ok().put("data", returnPage).putMpPageDescription().putDescription(EstateDoorCard.class);
        }
        return R.ok();
    }

    @ApiOperation(value = "excel导入模板下载")
    @RequestMapping(value = "/downloadExcel", method = RequestMethod.POST, produces = "application/octet-stream")
    public void downloadExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        File file = new File(excelTemplatePath + File.separator + "bluetoothCard.xls");
        String fileName = "物业蓝牙卡片信息导入模板.xls";
        // 读到流中
        InputStream inStream = new FileInputStream(file);// 文件的存放路径
        // 设置输出的格式
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename="
                + new String(fileName.getBytes("utf-8"), "iso-8859-1"));
        // 循环取出流中的数据
        byte[] b = new byte[1024];
        int len = 0;
        while ((len = inStream.read(b)) > 0) {
            response.getOutputStream().write(b, 0, len);
        }
        inStream.close();
    }

    @ApiOperation(value = "excel导入卡片")
    @RequestMapping(value = "/importUnitCardByExcel", method = RequestMethod.POST)
    @ApiImplicitParams({
            @ApiImplicitParam(name = "communityId", value = "社区id", required = true, dataType = "long", paramType = "form"),
            @ApiImplicitParam(name = "file", value = "excel文件", required = false, dataType = "file", paramType = "form"),
    })
    public R importUnitCardByExcel(HttpServletRequest request) {
        //声明 错误的 list
        List<UnitCardInfo> errList = new ArrayList<UnitCardInfo>();
        //  excel中 正确的 数据
        List<EstateUnitCard> excelList = new ArrayList<EstateUnitCard>();
        List<EstateUnitCard> insertList = new ArrayList<EstateUnitCard>();
        Long communityId = null;
        try {
            String factoryCode = request.getParameter("factoryCode");
            if (StringUtils.isBlank(factoryCode)) {
                factoryCode = "daHao";
            }
            String communityIdStr = request.getParameter("communityId");
            if (StringUtils.isBlank(communityIdStr)) {
                communityIdStr = request.getHeader("communityId");
            }
            if (StringUtils.isBlank(communityIdStr)) {
                return R.error(400, "社区id不能为空");
            }
            try {
                communityId = Long.parseLong(communityIdStr);
            } catch (NumberFormatException ex) {
                return R.error(400, "社区id类型错误");
            }
            EstateAgency community = agencyService.selectById(communityId);
            parameterIsNull(community, "没有该社区信息");
            if (community.getAgencyType() == null || community.getAgencyType() != 2) {
                return R.error(400, "该机构不属于社区");
            }
            List<UnitBuilding> unitList = unitBuildingService.selectList(new EntityWrapper<UnitBuilding>().eq("community_id", communityId));
            if (unitList == null || unitList.isEmpty()) {
                return R.error(400, "请先创建楼栋单元");
            }
            Map<String, Object> unitMap = new HashMap<String, Object>();
            for (int x = 0; x < unitList.size(); x++) {
                unitMap.put(unitList.get(x).getUnitName(), unitList.get(x));
            }
            List<Long> unitIds = new ArrayList<Long>();
            Date createDate = new Date();
            //直接使用，无需配置=》将当前上下文初始化给CommonsMutipartResolver(创建多部分解析器)
            CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());
            //设置编码
            multipartResolver.setDefaultEncoding("utf-8");
            //检查request中是否有 文件上传=》enctype="multipart/form-data"
            if (multipartResolver.isMultipart(request)) {
                //将request变成多部分request
                MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
                //获取multiRequest 中所有的文件名
                Iterator iter = multiRequest.getFileNames();
                boolean fileIterTag = true;
                while (iter.hasNext()) {
                    if (fileIterTag) {
                        fileIterTag = false;
                    }
                    //一次遍历所有文件
                    MultipartFile file = multiRequest.getFile(iter.next().toString());
                    if (file != null) {
                        String filePath = file.getOriginalFilename();
                        String fileType = filePath.substring(filePath.lastIndexOf(".") + 1, filePath.length());
                        InputStream stream = file.getInputStream();
                        Workbook wb = null;
                        if (fileType.equals("xls")) {
                            wb = new HSSFWorkbook(stream);
                        } else if (fileType.equals("xlsx")) {
                            wb = new XSSFWorkbook(stream);
                        } else {
                            return R.error(400, "该excel文件格式不正确");
                        }
                        //判断是否重复
                        Map<String, Object> distinctExcelDataMap = new HashMap<String, Object>();
                        Sheet sheet1 = wb.getSheetAt(0);
                        for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
                            Row titleRow = sheet1.getRow(0); //标题 不取
                            Row row = sheet1.getRow(i);
                            String unitName = ExcelReadUtil.getValue((Cell) row.getCell(0)).trim();//单元名称
                            String cardNumTen = ExcelReadUtil.getValue((Cell) row.getCell(1)).trim();//卡片编号 10进制
                            if (StringUtils.isBlank(unitName)) {
                                UnitCardInfo unitCardInfo = new UnitCardInfo();
                                unitCardInfo.setUnitName(unitName);
                                unitCardInfo.setCradNumTen(cardNumTen);
                                unitCardInfo.setErrInfo("单元名称为空");
                                errList.add(unitCardInfo);
                                continue;
                            }
                            if (StringUtils.isBlank(cardNumTen)) {
                                UnitCardInfo unitCardInfo = new UnitCardInfo();
                                unitCardInfo.setUnitName(unitName);
                                unitCardInfo.setCradNumTen(cardNumTen);
                                unitCardInfo.setErrInfo("卡片编号为空");
                                errList.add(unitCardInfo);
                                continue;
                            }

                            String cardNumSixteen = null;
                               /*================= 判断卡号是否重复=============== */
                            if (distinctExcelDataMap.get(cardNumTen) != null) {//去除重复的卡号
                                UnitCardInfo unitCardInfo = new UnitCardInfo();
                                unitCardInfo.setUnitName(unitName);
                                unitCardInfo.setCradNumTen(cardNumTen);
                                unitCardInfo.setErrInfo("卡片编号重复");
                                errList.add(unitCardInfo);
                                continue;
                            }
                            distinctExcelDataMap.put(cardNumTen, true);//没有重复 put进去
                                /*================= 卡号转16进制=============== */
                            try {
                                cardNumSixteen = Long.toHexString(Long.parseLong(cardNumTen));
                            } catch (NumberFormatException ex) {
                                UnitCardInfo unitCardInfo = new UnitCardInfo();
                                unitCardInfo.setUnitName(unitName);
                                unitCardInfo.setCradNumTen(cardNumTen);
                                unitCardInfo.setErrInfo("卡片编号错误");
                                errList.add(unitCardInfo);
                                continue;
                            }
                            //转换为16进制后，如果不足8位，前面补0
                            if (cardNumSixteen.length() < 8) {
                                int x = 8 - cardNumSixteen.length();
                                for (int z = 0; z < x; z++) {
                                    cardNumSixteen = "0" + cardNumSixteen;
                                }
                            }

                            /*================= 判断单元名称是否存在=============== */
                            UnitBuilding unit = (UnitBuilding) unitMap.get(unitName);
                            if (unit == null) {//单元名称出错
                                UnitCardInfo unitCardInfo = new UnitCardInfo();
                                unitCardInfo.setUnitName(unitName);
                                unitCardInfo.setCradNumTen(cardNumTen);
                                unitCardInfo.setErrInfo("未查询到单元名称");
                                errList.add(unitCardInfo);
                                continue;
                            }
                            EstateUnitCard unitCard = new EstateUnitCard();
                            unitCard.setCardNumTen(cardNumTen);
                            unitCard.setCardNumSixteen(cardNumSixteen);
                            unitCard.setCommunityId(unit.getCommunityId());
                            unitCard.setCommunityName(community.getAgencyName());
                            unitCard.setUnitId(unit.getUnitId());
                            unitCard.setUnitName(unit.getUnitName());
                            unitCard.setFactoryCode(factoryCode);
                            unitCard.setCreateTime(createDate);
                            excelList.add(unitCard);
                            if (!unitIds.contains(unit.getUnitId())) {
                                unitIds.add(unit.getUnitId());
                            }
                        }//for 循环遍历表格数据
                    }
                }
                if (fileIterTag) {
                    return R.error(400, "请上传卡片信息excel文件");
                }
            }//检查请求头是否有文件上传
            // 去除重复的
            if (!excelList.isEmpty()) {
                Wrapper<EstateUnitCard> mysqlUnitCardWp = new EntityWrapper<EstateUnitCard>();
                mysqlUnitCardWp.eq("community_id", communityId);
                mysqlUnitCardWp.in("unit_id", unitIds);
                List<EstateUnitCard> mysqlUnitCardList = estateUnitCardService.selectList(mysqlUnitCardWp);
                boolean mysqlUnitCardListTag = false;
                if (mysqlUnitCardList != null && !mysqlUnitCardList.isEmpty()) {
                    mysqlUnitCardListTag = true;
                }
                for (int x = 0, excelListSise = excelList.size(); x < excelListSise; x++) {
                    boolean tag = true;
                    if (mysqlUnitCardListTag) {
                        for (int y = 0; y < mysqlUnitCardList.size(); y++) {
                            if ((excelList.get(x).getCardNumTen()).equals(mysqlUnitCardList.get(y).getCardNumTen())) {
                                if (excelList.get(x).getUnitName().equals(mysqlUnitCardList.get(y).getUnitName())) {
                                    tag = false;
                                    break;
                                }
                            }
                        }
                    }
                    if (tag) {
                        insertList.add(excelList.get(x));
                    }
                    if (!tag) {
                        UnitCardInfo unitCardInfo = new UnitCardInfo();
                        unitCardInfo.setUnitName(excelList.get(x).getUnitName());
                        unitCardInfo.setCradNumTen(excelList.get(x).getCardNumTen());
                        unitCardInfo.setErrInfo("卡片已录入");
                        errList.add(unitCardInfo);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (!insertList.isEmpty()) {
            estateUnitCardService.insertBatch(insertList);
            /**
             * 同步清除 查询待发卡 卡片的缓存
             */
            estateDoorCardService.clearPreKeyCache("estateDoorCard:selectWillUseCardPage");
        }
        if (!errList.isEmpty()) {
            File csfile = new File(excelErrDaHaoCardPath + File.separator + communityId);
            if (!csfile.exists()) {
                csfile.mkdirs();
            }
            writeErrExcelData(errList, csfile.getPath());
        }
        return R.ok().put("errSize", errList.size()).putDescription("{errSize:导入数据的错误条数}");
    }

    @ApiOperation(value = "excel错误数据列表查询")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "communityId", value = "社区id", required = true, dataType = "long", paramType = "form"),
    })
    @RequestMapping(value = "/getErrExcel", method = RequestMethod.POST)
    public Object getErrExcel(HttpServletRequest request, ModelMap modelMap, Long communityId) throws Exception {
        parameterIsNull(communityId, "社区id不能为空");
        String realPathUrl = request.getServletContext().getRealPath("");
        File csfile = new File(excelErrDaHaoCardPath + File.separator + communityId);
        String[] names = csfile.list();
        return R.ok().put("data", names).put("description", "{data:错误数据Excel表格名称-数组}");
    }

    @ApiOperation(value = "错误数据下载")
    @RequestMapping(value = "/downloadErrExcel", produces = "application/octet-stream", method = RequestMethod.GET)
    @ApiImplicitParams({
            @ApiImplicitParam(name = "communityId", value = "社区id", required = true, dataType = "long", paramType = "form"),
            @ApiImplicitParam(name = "name", value = "excel文件名称", required = true, dataType = "string", paramType = "form"),
    })
    public void downloadErrExcel(HttpServletRequest request, HttpServletResponse response, String name, Long communityId) throws Exception {
        File csfile = new File(excelErrDaHaoCardPath + File.separator + communityId + File.separator + name);
        if (!csfile.exists()) {
            throw new RRException("该文件暂不存在", 400);
        }
        String fileName = name;
        // 读到流中
        InputStream inStream = new FileInputStream(csfile);// 文件的存放路径
        // 设置输出的格式
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=GB2312");
        response.setCharacterEncoding("GB2312");
        response.setHeader("Content-Disposition", "attachment; filename="
                + new String(fileName.getBytes("GB2312"), "iso-8859-1"));
        // 循环取出流中的数据
        byte[] b = new byte[1024];
        int len = 0;
        while ((len = inStream.read(b)) > 0) {
            response.getOutputStream().write(b, 0, len);
        }
        inStream.close();
    }

    /**
     * 将 错误数据 存在本地
     *
     * @param list
     * @param url
     * @throws Exception
     */
    public void writeErrExcelData(List<UnitCardInfo> list, String url) {
        HSSFWorkbook wb = new HSSFWorkbook();
        // 在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("物业管理资源");
        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.RED.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = wb.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = wb.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("单元名称");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("卡片编号");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("错误信息");
        cell.setCellStyle(style);
        // 写入实体数据 实际应用中这些数据从数据库得到，
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow((int) i + 1);
            row.createCell((short) 0).setCellValue(list.get(i).getUnitName());
            row.createCell((short) 1).setCellValue(list.get(i).getCradNumTen());
            row.createCell((short) 2).setCellValue(list.get(i).getErrInfo());
        }
        // 第六步，将文件存到指定位置
        try {
            FileOutputStream fout = new FileOutputStream(url + File.separator + DateUtil.format(new Date(), DateUtil.DATE_PATTERN.YYYYMMDDHHMMSS) + "蓝牙卡片信息导入错误数据.xls");
            logger.info("exportExcel, fileName:" + fout);
            wb.write(fout);
            fout.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}